<?php

class mscan extends CI_Model {

    /**
     * ambil record pada table t_trx_scan_in berdasarkan id
     * @param scan_in_id $id
     * @return type 
     */
    public function getById($id) {
        $id = intval($id);
        $dbconn = $this->load->database("default", TRUE);
		$sql = "SELECT scan_in_id, user_id, scan_date, istatus, created_by, created_on, updated_by, ".
		 		"updated_on, acess_via, date_trunc('second', created_on::time ) as data_timestamp " .
				"FROM t_trx_scan_in " .
                "WHERE scan_in_id='$id'";

        $query = $dbconn->query($sql);
        
        //$query = $this->db->where('scan_in_id', $id)->limit(1)->get('t_trx_scan_in');

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return array();
        }
    }

    public function getPdfDetails($scan_id){
        $listItem = array();
		$dbconn = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');

        $sql =  "SELECT imp.item_code as item_code, count(imp.item_code) as jumlah, ".
                "itm.item_name, grp.item_group_name, f_get_price(imp.item_code, h.user_id) as harga ".
                "FROM t_trx_scan_in_detail d ".
                "JOIN t_mtr_item itm ON itm.iccid = split_part(d.iccid,'#',1) ".
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code ".
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id ".
                "JOIN t_trx_scan_in h ON d.scan_in_id = h.scan_in_id ".
                "WHERE h.scan_in_id = '$scan_id' ".
                "GROUP BY imp.item_code, itm.item_name, grp.item_group_id, h.user_id";

		$query = $dbconn->query($sql);
        return $query->result();
    }


    public function getIccidByScanId($scan_id) {
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        // $sql = "SELECT split_part(iccid,'#',1) ".
        //         "FROM t_trx_scan_in_detail ".
        //         "WHERE scan_in_id = $scan_id ";

// SELECT split_part(d.iccid,'#',1) AS iccid, itm.item_name, itm.item_code, grp.item_group_id, itm.mdn,
// CAST(f_get_price(split_part(d.iccid,'#',1), '10003752') AS INTEGER), grp.item_group_name 
// FROM t_trx_scan_in_detail d
// JOIN t_mtr_item itm ON itm.iccid = split_part(d.iccid,'#',1)
// JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code
// JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id
// WHERE d.scan_in_id ='168'

        $sql = "SELECT split_part(d.iccid,'#',1) iccid, itm.item_name, itm.item_code, grp.item_group_id, itm.mdn, ".
                "CAST(f_get_price(split_part(d.iccid,'#',1), h.user_id) AS INTEGER), grp.item_group_name " .
                "FROM t_trx_scan_in_detail d ".
                "join t_trx_scan_in as h on h.scan_in_id = d.scan_in_id ".
                "JOIN t_mtr_item itm ON itm.iccid = split_part(d.iccid,'#',1) ".
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code " .
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id " .
                "WHERE d.scan_in_id =' $scan_id' ";

        $query = $dbconn->query($sql);
        return $query->result();
    }
  // tampilkan detail scan in dalam satu kali query
  public function scan_details($ID)
  {
    $this->db->select('f_get_user(user_id) as user_name, f_get_territory_path(f_get_cluster(user_id)) as territory_name, *');
    $this->db->where('scan_in_id', $ID);
    $q = $this->db->get('t_trx_scan_in');
    $data["header"] = $q->row();
    
// SELECT
//     split_part(d.iccid, '#', 1) iccid, itm.item_name, itm.item_code, grp.item_group_id, itm.mdn,
//     CAST (f_get_price ( split_part(d.iccid, '#', 1), h.user_id) AS INTEGER),
//     grp.item_group_name
//     FROMt_trx_scan_in_detail d
//     JOIN t_trx_scan_in AS h ON h.scan_in_id = d.scan_in_id
//     JOIN t_mtr_item itm ON itm.iccid = split_part(d.iccid, '#', 1)
//     JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code
//     JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id
//     WHERE   d.scan_in_id = '274404'

    $this->db->select(" split_part(dd.iccid, '#', 1) as iccid, ii.item_name, ii.item_code, gg.item_group_name, ii.mdn ", false);
    $this->db->select(" CAST (f_get_price ( split_part(dd.iccid, '#', 1), hh.user_id) AS INTEGER) ", false);
    $this->db->join('t_trx_scan_in hh', 'hh.scan_in_id = dd.scan_in_id');
    $this->db->join("t_mtr_item ii", "ii.iccid = split_part(dd.iccid, '#', 1)");
    $this->db->join("t_mtr_item_group gg", "gg.item_group_id = ii.item_group_id");
    $this->db->where('dd.scan_in_id', $ID);
    $q = $this->db->get('t_trx_scan_in_detail dd');
    $data["detail"] = $q->result();

    return $data;
  }

    //ambil record pada table t_mtr_item dan t_mtr_item_group
    public function getItemDetail($id) {
        $listItem = array();
        //anggwa@gmail.com 20120513
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $sql = "SELECT itm.item_name, itm.item_code, grp.item_group_id, itm.mdn, " .
                "CAST(f_get_price(itm.iccid, h.user_id) AS INTEGER), grp.item_group_name " .
                "FROM t_mtr_item itm join t_trx_scan_in h on h.scan_in_id = item.scan_in_id ".
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code " .
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id " .
                "WHERE itm.iccid = split_part('$id','#',1) LIMIT 1";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $listItem = $query->result_array();
        } else {
            return array();
        }
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $res[0]['item_group_id']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();	
        // $userlogin = $this->session->userdata('username');
        // $this->db->select("item_name, item_code, item_group_id, mdn, f_get_price(item_code, '$userlogin')");
        // $this->db->where('iccid', $id);
        // $q = $this->db->get('t_mtr_item');
        // $listItem = $q->result_array();	
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $listItem[0]['item_group_id']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();	
        // $listItem[0]['item_group_name'] = $q[0]['item_group_name'];
        return $listItem;
    }

    //ambil semua record pada table t_trx_scan_in
    public function getAllScanin($cluster_id = '') {

        $res = array();
        //anggwa@gmail.com 20120513
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        
//         $sql = "SELECT * FROM t_trx_scan_in " .
//                "WHERE DATE_PART('day', now() - scan_date::timestamp) " .
//                "BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month')) " .
//                "AND f_get_cluster(user_id) = f_get_cluster('$userlogin') " .
//                "ORDER BY scan_in_id DESC, scan_date desc";
    //      $sql = "SELECT scan_in_id, user_id, scan_date,  " .
		 	// 	"date_trunc('second', created_on::time ) as data_timestamp, " .
				// "created_on FROM t_trx_scan_in " .
    //             "WHERE scan_date = CURRENT_DATE AND f_get_cluster(user_id) = '$cluster_id' " .
    //             "ORDER BY scan_in_id DESC, scan_date desc";

        /** @edited Alfin Akhret <alfin.akhret@gmail.com>
            /* optimasi query
            /** SSP
           */
        /** ssp variable **/
        $sIndexColumn = "scan_in_id"; //index column untuk kecepatan akses data
        $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
        $aColumns = array("scan_date", "data_timestamp", "scan_in_id", "user_name"); //field2 yg akan ditampilkan pada dataTab
        $flag = '';

        $inputQuery = "
            SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM v_ssp_scan_in_history WHERE scan_date = CURRENT_DATE AND f_get_cluster(user_id) = $cluster_id";

        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn);

        // $query = $dbconn->query($sql);
        
        // //$query = $this->db->get( 't_trx_scan_in' );


        // if ($query->num_rows() > 0) {
        //     foreach ($query->result() as $res) {
        //         $res->getNikName = $this->getNikName($res->user_id);
        //     }
        //     return $query->result();
        // } else {
        //     return array();
        // }
    }

    //edited by Alfin Akhret
    public function getAllScaninCurrentUser() {

//         $res = array();
//         //anggwa@gmail.com 20120513
//         $dbconn = $this->load->database("default", TRUE);
//         $userlogin = $this->session->userdata('username');
// //         $sql = "SELECT * FROM t_trx_scan_in " .
// //                "WHERE DATE_PART('day', now() - scan_date::timestamp) " .
// //                "BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month')) " .
// //                "AND f_get_cluster(user_id) = f_get_cluster('$userlogin') " .
// //                "ORDER BY scan_in_id DESC, scan_date desc";
//         $sql = "SELECT scan_in_id, user_id, scan_date,  " .
//                 "date_trunc('second', created_on::time ) as data_timestamp, " .
//                 "created_on FROM t_trx_scan_in " .
//                 "WHERE scan_date = CURRENT_DATE AND user_id = '$userlogin' " .
//                 "ORDER BY scan_in_id DESC, scan_date desc";

//         $query = $dbconn->query($sql);
//         //$query = $this->db->get( 't_trx_scan_in' );


//         if ($query->num_rows() > 0) {
//             foreach ($query->result() as $res) {
//                 $res->getNikName = $this->getNikName($res->user_id);
//             }
//             return $query->result();
//         } else {
//             return array();
//         }

        $res = array();
        //anggwa@gmail.com 20120513
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');

        $sIndexColumn = "scan_in_id"; //index column untuk kecepatan akses data
        $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
        $aColumns = array("scan_date", "data_timestamp", "scan_in_id", "user_name"); //field2 yg akan ditampilkan pada dataTab
        $flag = '';

        $inputQuery = "
            SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM v_ssp_scan_in_history_smile WHERE scan_date = CURRENT_DATE AND user_id = '".$userlogin."'";

        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn);

    }

    public function validate_item($item, $sales_id)
    {
       $this->db->select("f_check_valid_item('$item', '$sales_id') as valid_item", false);
       $valid = $this->db->get();

       return $valid->row()->valid_item;
    }
//end getAll
    //save hasil insert dari form ke dalam table t_trx_scan_in 
    function saveScan() {

        $dblokal = $this->load->database("default", true);
        $scan_date = $this->input->post("scan_date");
        $sales_id = $this->input->post("sales_id");
        $quantity = $this->input->post("quantity");
        $remark = $this->input->post("remark");
        $salesIdExplode = explode(" ", $sales_id);

        $data = array(
            "scan_date" => $scan_date,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username'),
            "user_id" => trim($salesIdExplode[0]),
            "acess_via" => "web",
            "istatus" => 1
        );
        $dblokal->insert("t_trx_scan_in", $data);
        $last = $this->db->insert_id();
        $this->mglobal->saveLog('scan-in', 'create', $last);

        return $last;
    }

    //save hasil insert dari form ke dalam table t_trx_scan_in_detail
    function saveScanInDetail($sid, $i) {

        $dblokal = $this->load->database("default", true);
        $iccid = $this->input->post('iccid');
        //$mdn = $this->input->post('mdn');
        $item_group_name = $this->input->post('item_group_name');
        $item_name = $this->input->post('item_name');

        $data = array(
            "scan_in_id" => $sid,
            "iccid" => $iccid[$i],
            "istatus" => 1
        );

        $dblokal->insert("t_trx_scan_in_detail", $data);
        $last = $this->db->insert_id();
        $this->mglobal->saveLog('scan-in-detail', 'create', '(header=' . $sid . ' detail=' . $last . ' iccid=' . $iccid[$i] . ')');

        //update istatus pada table t_mtr_item menjadi 2
        $data2 = array(
            'istatus' => 2,
        );
        $this->db->update('t_mtr_item', $data2, array('iccid' => $iccid[$i]));
    }

    // yaddis@nutech-integrasi.com --- 07/21/2012
    public function save_scan_in($sales_id) {
        $dblokal = $this->load->database("default", true);

        $scan_date = $this->input->post("scan_date");

        $data = array(
            "scan_date" => $scan_date,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username'),
            "user_id" => $sales_id,
            "acess_via" => "web",
            "istatus" => 1
        );
        $dblokal->insert("t_trx_scan_in", $data);
        $last = $this->db->insert_id();
        $this->mglobal->saveLog('scan-in', 'create', $last);

        return $last;    
    }
    public function save_scan_in_detail($scan_in_id, $item)
    {
        $data = array(
                "scan_in_id" => $scan_in_id,
                "iccid" => $item,
                "istatus" => 1
            );
        $this->db->insert("t_trx_scan_in_detail", $data);
        $last = $this->db->insert_id();

        $this->mglobal->saveLog('scan-in-detail', 'create', '(header=' . $scan_in_id . ' detail=' . $last . ' iccid=' . $item . ')');

        //update istatus pada table t_mtr_item menjadi 2
        $data2 = array( 'istatus' => 2 );
        $this->db->update('t_mtr_item', $data2, array('iccid' => $item));
    }

    //ambil nama territory dari id user yang bersangkutan
    public function getClusterName($id = '') {
      $dbconn = $this->load->database("default", TRUE);
      $sql = "SELECT f_get_territory_path(f_get_cluster('$id')) as territory_name, f_get_cluster('$id') as territory_id";
      $query = $dbconn->query($sql);
      if ($query->num_rows() > 0) 
        { return $query->row_array(); } 
      else 
        { return array(); }
    }

  public function ajaxValidate($sales_id, $iccid) {
    $userlogin = $this->session->userdata('username');
    $data['item_detail'] = $this->_item_detail($userlogin, $sales_id, $iccid);
    $data['messege']     = $this->_messege($userlogin, $sales_id, $iccid);
    return $data;
  }

  private function _item_detail($userlogin, $sales_id, $iccid)
  {
    $this->db->select("ii.mdn, ii.item_name, gg.item_group_name, CAST(f_get_price(ii.item_code, '$sales_id') AS INTEGER) as price", false);
    $this->db->join('t_mtr_item_mapping mm', 'ii.item_code = mm.item_code');
    $this->db->join('t_mtr_item_group gg', 'mm.item_group_id = gg.item_group_id');
    $this->db->where('ii.iccid', $iccid);
    $query = $this->db->get('t_mtr_item ii');
    return $query->row();
  }

  private function _messege($userlogin, $sales_id, $iccid)
  {
    $code = $this->checkValid($iccid, $sales_id);
    $messege = $this->_pesan_error($code);
    return $messege;
  }

  private function _pesan_error($error_code) {
      $this->db->select("f_check_valid_item_error_code($error_code) AS messege");
      $query = $this->db->get();
      return $query->row()->messege;
  }

    // // Ambil Credit Limit (maksimum) dari Sales ($sales_id)
    function getCreditLimit($sales_id) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = ("SELECT f_get_credit_limit('$sales_id')");
        $query = $dbconn->query($sql);
        return $query->row()->f_get_credit_limit;
    }

    // Ambil Current Credit Limit dari Sales ($sales_id)
    function getCurrentLimit($sales_id) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = ("SELECT f_get_current_limit('$sales_id')");
        $query = $dbconn->query($sql);
        return $query->row()->f_get_current_limit;
    }

    //get nik name from user
    function getNikName($userID) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_nik_name('$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_nik_name'];
        } else {
            return array();
        }
    }

    //get item price for each sales
    function getPrice($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT CAST(f_get_price(split_part('$iccid','#',1),'$userID') AS INTEGER)";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_price'];
        } else {
            return array();
        }
    }

    //get item price for each sales
    function checkValid($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_check_valid_item(split_part('$iccid','#',1),'$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_check_valid_item'];
        } else {
            return array();
        }
    }

    //ambil daftar iccid dari barang-barang yang berada pada cluster yang bersangkutan, untuk ditampilkan pada textbox autocomplete
    function getRelatedIccid($salesId = "", $term = "") {
        $cluster_id = $this->mglobal->user_cluster( $salesId );
        $userlogin = $this->session->userdata('username');
        $dbconn = $this->load->database("default", TRUE);

        $sql = "select itm.iccid " .
                "from t_mtr_item as itm " .
                "join t_mtr_distributor as dis on " .
                "itm.distributor_id = dis.distributor_id " .
                "AND dis.territory_id = $cluster_id  " .
                "where itm.iccid like '$term%' " .
                "AND itm.istatus = 1 " .
                "ORDER BY iccid ASC LIMIT 10";

        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }

    public function get_sales($login_user)
    {
        // echo $login_user; die();
        $cluster = $this->db->get("f_get_cluster('$login_user')")->row()->f_get_cluster;
        $this->db->select("t_mtr_user.user_id, t_mtr_user.user_name, t_mtr_user_group.user_group_name, t_mtr_user_group.user_group_caption");
        $this->db->join("t_mtr_user_group","t_mtr_user_group.user_group_id = t_mtr_user.user_group_id");
        $this->db->where("f_get_cluster(t_mtr_user.user_id)", $cluster);
        $this->db->where("t_mtr_user_group.is_use_credit_limit", 1 );
        $query = $this->db->get("t_mtr_user");

        return $query->result();
    }

    public function credit_limit($sales_id)
    {
        $userlogin = $this->session->userdata("username");

        $this->db->select("t_mtr_user.user_id, t_mtr_user.user_name, t_mtr_user_group.user_group_name");
        $this->db->select("f_get_current_limit('$sales_id') as current_limit", false);
        $this->db->select("f_get_credit_limit('$sales_id') as credit_limit", false);
        $this->db->select("f_get_credit_limit_minmax( '$userlogin') as limit_allowed", false);
        $this->db->join("t_mtr_user_group","t_mtr_user_group.user_group_id = t_mtr_user.user_group_id");
        // $this->db->join("t_mtr_credit_limit","t_mtr_credit_limit.user_id = t_mtr_user.user_id");


        $query = $this->db->get_where("t_mtr_user",array("t_mtr_user.user_id" => $sales_id));
        return $query->row_array();
    }

    public function credit_limit_history($sales_id)
    {
        $this->db->select("t_mtr_user.user_name, t_mtr_credit_limit.limit_date, t_mtr_credit_limit.limit_value, t_mtr_credit_limit.remark");
        $this->db->join("t_mtr_user","t_mtr_user.user_id = t_mtr_credit_limit.created_by");
        $this->db->order_by("t_mtr_credit_limit.created_on","desc");
        $this->db->limit(10);
        $query = $this->db->get_where("t_mtr_credit_limit", array("t_mtr_credit_limit.user_id" => $sales_id));

        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return false;
        }
    }
    // userlogin could be CC, CA, RH, or anybody, so u must select all sales with the same cluster to the current login user
    public function get_all_credit_limit($userlogin) 
    {
        $cluster = $this->db->get("f_get_cluster('$userlogin')")->row()->f_get_cluster;

        $this->db->select("t_mtr_user.user_id, initcap(t_mtr_user.user_name) as user_name, t_mtr_user_group.user_group_name");
        $this->db->select("f_get_current_limit(t_mtr_user.user_id) as current_limit", false);
        $this->db->select("f_get_credit_limit(t_mtr_user.user_id) as limit_allowed", false);
        $this->db->join("t_mtr_user_group","t_mtr_user_group.user_group_id = t_mtr_user.user_group_id");
        $this->db->where("f_get_cluster(t_mtr_user.user_id)", $cluster);
        $this->db->where("t_mtr_user_group.is_use_credit_limit", 1);
        $this->db->where("t_mtr_user.istatus", 1);
        $query = $this->db->get("t_mtr_user");
        return $query->result();
    }

    function save_new_limit($sales_id, $new_limit, $remark) {
        $this->db->trans_begin();

        $data = array(
            'user_id' => $sales_id,
            'limit_value' =>  $new_limit,
            'limit_date' => date('Y-m-d'),
            'created_by' => $this->session->userdata("username"),
            'remark' => $remark
            );

        $this->db->insert("t_mtr_credit_limit", $data);

        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            return false;
        }
        else
        {
            $this->db->trans_commit();
            return true;
        }

    }
}

//end class